package com.xszx.dao.impl;

import com.xszx.beans.Goods;
import com.xszx.beans.Orders;
import com.xszx.beans.dto.OrdersDto;
import com.xszx.dao.OrdersDao;
import com.xszx.util.JDBCUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class OrdersDaoImpl implements OrdersDao {
    @Override
    public List<OrdersDto> getOrders(String username, Integer state) {
        try {
            Connection connection = JDBCUtil.getConnection();
            String sql = "select * from orders where username = ? and state = ?";
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, username);
            ps.setInt(2, state);

            ResultSet resultSet = ps.executeQuery();
            List<OrdersDto> list = new ArrayList<>();
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                int goodsId = resultSet.getInt("goods_id");

                Goods goods = new GoodsDaoImpl().getGoods(goodsId);

                OrdersDto ordersDto = new OrdersDto(id, username, goodsId, state, goods.getGoodsName(), goods.getGoodsPrice(), goods.getGoodsImg());
                list.add(ordersDto);
            }

            connection.close();

            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return null;
    }

    @Override
    public void addOrders(String username, Integer id, Integer num) {
        try {
            Connection connection = JDBCUtil.getConnection();
            String sql = "insert into orders values(null, ?, ?, 0, null, ?)";
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, username);
            ps.setInt(2, id);
            ps.setInt(3, num);

            ps.execute();

            connection.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public int getOrderNum(String username) {
        try {
            Connection connection = JDBCUtil.getConnection();
            String sql = "select count(id) as num from orders where username = ?";
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, username);

            ResultSet resultSet = ps.executeQuery();
            resultSet.next();
            int num = resultSet.getInt("num");

            connection.close();

            return num;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public List<Integer> getOrderState(String username) {
        try {
            Connection connection = JDBCUtil.getConnection();
            String sql = "select state from orders where username = ?";
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, username);

            ResultSet resultSet = ps.executeQuery();
            List<Integer> list = new ArrayList<>();
            while (resultSet.next()) {
                list.add(resultSet.getInt("state"));
            }

            connection.close();

            List<Integer> list1 = new ArrayList<>();

            list1.add((int)list.stream().filter(num -> num == 0).count());
            list1.add((int)list.stream().filter(num -> num == 1).count());
            list1.add((int)list.stream().filter(num -> num == 2).count());
            list1.add((int)list.stream().filter(num -> num == 3).count());
            list1.add((int)list.stream().filter(num -> num == 4).count());

            return list1;
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return null;
    }

    @Override
    public double getOrderMoney(String username) {
        try {
            Connection connection = JDBCUtil.getConnection();
            String sql = "select sum(o.num) as num, t.goodsPrice as price from orders o, t_goods t where username = ? and o.goods_id = t.id GROUP BY t.goodsPrice";
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, username);

            ResultSet resultSet = ps.executeQuery();
            int res = 0;
            while (resultSet.next()) {
                int num = resultSet.getInt("num");
                int price = resultSet.getInt("price");
                res += num * price;
            }

            connection.close();

            return res;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public List<OrdersDto> getAllOrders() {
        try {
            Connection connection = JDBCUtil.getConnection();
            String sql = "select o.id, o.username, o.state, o.num, t.goodsName, t.goodsPrice, t.goodsImg from orders o, t_goods t where o.goods_id = t.id;";
            PreparedStatement ps = connection.prepareStatement(sql);

            ResultSet resultSet = ps.executeQuery();
            List<OrdersDto> list = new ArrayList<>();
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String username = resultSet.getString("username");
                int state = resultSet.getInt("state");
                int num = resultSet.getInt("num");
                double goodsPrice = resultSet.getDouble("goodsPrice");
                String goodsName = resultSet.getString("goodsName");
                String goodsImg = resultSet.getString("goodsImg");

                OrdersDto ordersDto = new OrdersDto(id, username, state, num, goodsName, goodsPrice, goodsImg);
                list.add(ordersDto);
            }

            connection.close();

            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return null;
    }

    @Override
    public void updateOrderState(int id) {
        try {
            Connection connection = JDBCUtil.getConnection();
            String sql = "update orders set state = 2 where id = ?;";
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setInt(1, id);

            ps.execute();

            connection.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public List<OrdersDto> selectOrders(String name, int selectState) {
        try {
            Connection connection = JDBCUtil.getConnection();
            String sql = "select o.id, o.username, o.state, o.num, t.goodsName, t.goodsPrice, t.goodsImg from orders o, t_goods t where o.goods_id = t.id and t.goodsName like ?";

            if (selectState >= 0) {
                sql += " and o.state = ?";
            }
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, "%" + name + "%");
            if (selectState >= 0) {
                ps.setInt(2, selectState);
            }

            ResultSet resultSet = ps.executeQuery();
            List<OrdersDto> list = new ArrayList<>();
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String username = resultSet.getString("username");
                int state = resultSet.getInt("state");
                int num = resultSet.getInt("num");
                double goodsPrice = resultSet.getDouble("goodsPrice");
                String goodsName = resultSet.getString("goodsName");
                String goodsImg = resultSet.getString("goodsImg");

                OrdersDto ordersDto = new OrdersDto(id, username, state, num, goodsName, goodsPrice, goodsImg);
                list.add(ordersDto);
            }

            connection.close();

            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return null;
    }
}
